/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package wads.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
import java.sql.Statement;
import java.util.Properties;
import wads.com.UserInfo;
import wads.com.UserInfoDataStruct;

/**
 *
 * @author ManowTangdud
 */
public class MySql {
    private String driver = "com.mysql.jdbc.Driver";
    private String protocol = "jdbc:mysql://";
    private String server = "db4free.net:3306/";
    private String user = "xenogew";
    private String password = "widwacom";
    private String database = "wads";
    private Connection conn;
    private Properties prop;
    private boolean dbServerOn;
    private String proxyAddress = "", proxyPort = "";
    private boolean useProxy;

    public MySql() {

//        prop = WADTools.loadWadConfig();
//        // load config
//        if(prop != null) {
//            server = prop.getProperty(WADTools.DBSERVER_NAME_KEYNAME)+"/";
//        }

        startServer();
    }

    public MySql(boolean useProxy, String proxyAddress, String proxyPort) {
        this.useProxy = useProxy;
        this.proxyAddress = proxyAddress;
        this.proxyPort = proxyPort;

        startServer();
    }

    private void startServer() {

        try {
            // load driver
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver.");

            conn = DriverManager.getConnection(protocol+server+database,user, password);

            setDbServerOn(true);
        }
        catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (InstantiationException ie) {
            System.err.println("\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (IllegalAccessException iae) {
            System.err.println("\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
            setDbServerOn(false);
        }
        catch (SQLNonTransientConnectionException ex) {
            System.err.println("\nCannot connect to the database " + server);
            ex.printStackTrace(System.err);
            setDbServerOn(false);
        }        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
            setDbServerOn(false);
        }
    }

    public UserInfo loadUsers() {
        UserInfo members = new UserInfo();
        UserInfoDataStruct member;

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM users";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                member = new UserInfoDataStruct();
                System.out.println(rs.getInt("uid"));
                member.setId(rs.getInt("uid"));
                System.out.println(rs.getString("username"));
                member.setUsername(rs.getString("username"));
                System.out.println(rs.getBytes("password"));
                member.setEncryptedPassword(rs.getBytes("password"));
                System.out.println(rs.getString("name"));
                member.setName(rs.getString("name"));
                System.out.println(rs.getString("position"));
                member.setPosition(rs.getString("position"));
                System.out.println(rs.getString("email"));
                member.setEMail(rs.getString("email"));
                System.out.println(rs.getString("roles")+"\n");
                member.setRoles(rs.getString("roles"));

                members.addData(member);
            }
            stmt.close();

            return members;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    // flag parameter can be 'pu' = Powered User, 'ru' = Restricted User, 'admin' = Administrator
    public UserInfo loadUsers(String flag) {
        UserInfo members = new UserInfo();
        UserInfoDataStruct member;

        try {
//            // load config
//            if(prop != null) {
//                setServer(prop.getProperty(WADTools.DBSERVER_NAME_KEYNAME) + "/");
//            }

            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM users WHERE roles = '"+flag+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                member = new UserInfoDataStruct();
                System.out.println(rs.getInt("uid"));
                member.setId(rs.getInt("uid"));
                System.out.println(rs.getString("username"));
                member.setUsername(rs.getString("username"));
                System.out.println(rs.getBytes("password"));
                member.setEncryptedPassword(rs.getBytes("password"));
                System.out.println(rs.getString("name"));
                member.setName(rs.getString("name"));
                System.out.println(rs.getString("position"));
                member.setPosition(rs.getString("position"));
                System.out.println(rs.getString("email"));
                member.setEMail(rs.getString("email"));
                System.out.println(rs.getString("roles")+"\n");
                member.setRoles(rs.getString("roles"));

                members.addData(member);
            }
            stmt.close();

            return members;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    public UserInfoDataStruct loadUser(String username) {

        UserInfoDataStruct member;

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM users WHERE username = '"+username+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            member = new UserInfoDataStruct();
            System.out.println(rs.getInt("uid"));
            member.setId(rs.getInt("uid"));
            System.out.println(rs.getString("username"));
            member.setUsername(rs.getString("username"));
            System.out.println(rs.getBytes("password"));
            member.setEncryptedPassword(rs.getBytes("password"));
            System.out.println(rs.getString("name"));
            member.setName(rs.getString("name"));
            System.out.println(rs.getString("position"));
            member.setPosition(rs.getString("position"));
            System.out.println(rs.getString("email"));
            member.setEMail(rs.getString("email"));
            System.out.println(rs.getString("roles")+"\n");
            member.setRoles(rs.getString("roles"));

            stmt.close();

            return member;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return null;
    }

    public void insertUser(UserInfoDataStruct userinfo) {

        try {
            // start query process
            String query = "INSERT INTO users (username,password,name,position,email,roles) VALUES (?,?,?,?,?,?)";
            PreparedStatement stmt = conn.prepareStatement(query);

            System.out.println(userinfo.toString());
            stmt.setString(1, userinfo.getUsername());
            stmt.setBytes(2, userinfo.getEncryptedPassword());
            stmt.setString(3, userinfo.getName());
            stmt.setString(4, userinfo.getPosition());
            stmt.setString(5, userinfo.getEMail());
            stmt.setString(6, userinfo.getRoles());

            stmt.execute();

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public void updateUser(UserInfoDataStruct userinfo) {

        try {
            // start query process
            String query = "UPDATE users SET username=?,password=?,name=?,position=?,email=?,roles=? WHERE uid=?";
            PreparedStatement stmt = conn.prepareStatement(query);

            System.out.println(userinfo.toString());
            stmt.setString(1, userinfo.getUsername());
            stmt.setBytes(2, userinfo.getEncryptedPassword());
            stmt.setString(3, userinfo.getName());
            stmt.setString(4, userinfo.getPosition());
            stmt.setString(5, userinfo.getEMail());
            stmt.setString(6, userinfo.getRoles());
            stmt.setInt(7, userinfo.getId());

            stmt.execute();

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public void deleteUser(int uid) {

        try {
            // start query process
            String query = "DELETE FROM users WHERE uid="+uid;
            System.out.println("Query String: "+query);
            Statement stmt = conn.createStatement();

            stmt.execute(query);

            stmt.close();
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }
    }

    public boolean isDuplicated(String username) {

        try {
            // start query process
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM users WHERE username = '"+username+"'";
            System.out.println("Query String: "+query);
            ResultSet rs = stmt.executeQuery(query);

            if(rs.next()) {
                return true;
            }

            stmt.close();

            return false;
        }
        catch (SQLException sqlex) {
            System.err.println("\nUnable process querying.");
            sqlex.printStackTrace(System.err);
        }

        return false;
    }

    /**
     * @return the dbServerOn
     */
    public boolean isDbServerOn() {
        return dbServerOn;
    }

    /**
     * @param dbServerOn the dbServerOn to set
     */
    public void setDbServerOn(boolean dbServerOn) {
        this.dbServerOn = dbServerOn;
    }

    /**
     * @return the server
     */
    public String getServer() {
        return server;
    }

    /**
     * @param server the server to set
     */
    public void setServer(String server) {
        this.server = server;
    }

    /**
     * @return the user
     */
    public String getUser() {
        return user;
    }

    /**
     * @param user the user to set
     */
    public void setUser(String user) {
        this.user = user;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * @return the database
     */
    public String getDatabase() {
        return database;
    }

    /**
     * @param database the database to set
     */
    public void setDatabase(String database) {
        this.database = database;
    }

    /**
     * @return the proxyAddress
     */
    public String getProxyAddress() {
        return proxyAddress;
    }

    /**
     * @param proxyAddress the proxyAddress to set
     */
    public void setProxyAddress(String proxyAddress) {
        this.proxyAddress = proxyAddress;
    }

    /**
     * @return the proxyPort
     */
    public String getProxyPort() {
        return proxyPort;
    }

    /**
     * @param proxyPort the proxyPort to set
     */
    public void setProxyPort(String proxyPort) {
        this.proxyPort = proxyPort;
    }

    /**
     * @return the useProxy
     */
    public boolean isUseProxy() {
        return useProxy;
    }

    /**
     * @param useProxy the useProxy to set
     */
    public void setUseProxy(boolean useProxy) {
        this.useProxy = useProxy;
    }
}
